/* Data4.do (STATA)
	Construct final data.
	by Ralph Koijen & Motohiro Yogo */

#delimit ;
clear all;
set more off;
set type double;

cap log close;
log using Data4, replace;

/* Define local and global variables */

do global;


/* Step 1: Merge characteristics and asset prices */

/* Load holdings data */

u Data3, clear;

/* Merge investor country characteristics */

merge m:1 year country using Data0,
	keepusing(Name MSCI Ieuro)
	nogen keep(master match);

/* Rename variables */

foreach var of varlist Name MSCI Ieuro {;
	rename `var' country_`var';
};

/* Merge asset prices */

merge m:1 year counterpart type using Data1,
	keepusing(maturity yield mb _ret)
	nogen keep(master match);

/* Merge issuer country characteristics */

merge m:1 year counterpart using Data0,
	keepusing(Name MSCI Ieuro fx relpi realfx gdpP gdpPpc pop drate vol Gcpi _Gfx _Gcpi _GconsRpc)
	nogen keep(master match);

/* Merge GeoDist data */

merge m:1 country counterpart using GeoDist,
	nogen keep(master match);


/* Step 2: Construct variables */

/* Fix variables */

replace country_Name = "Reserves" if country=="_CR";
replace country_Name = "Other" if country=="_OC";

recode country_Ieuro dist (. = 0);

/* Define investor country group */

gen byte country_group = 0 if country=="_CR";
replace country_group = country_MSCI if inrange(country_MSCI,1,3);
replace country_group = 4 if inrange(country_MSCI,4,6);
replace country_group = 5 if inrange(country_MSCI,7,12) | country=="_OC";

/* Define issuer country group */

gen byte group = MSCI if inrange(MSCI,1,3);
replace group = 4 if inrange(MSCI,4,6);

gen counter_euro = counterpart;
replace counter_euro = "EUR" if (Ieuro==1 | counterpart=="DNK") & type==1;
replace counter_euro = "USA" if counterpart=="HKG" & type==1;

/* Construct quantities */

gen Qamount = amount/(mb*fx);

egen market = total(amount), missing by(year counter_euro type);

gen Qmarket = market/(mb*fx);
gen book = market/mb;

egen quant = total(amount/(mb*realfx)), missing by(year counterpart type);

/* Construct wealth */

egen wealth = total(amount), missing by(year country);
egen wealthA = total(amount), missing by(year country type);
egen wealth3 = total(amount*(type==3)), missing by(year country);

egen outside = total(amount*(counterpart=="_OC")), missing by(year country);
egen outsideA = total(amount*(counterpart=="_OC")), missing by(year country type);

/* Drop other countries */

drop if counterpart=="_OC";

/* Construct ratios of wealth */

gen rw = amount/outsideA;

egen Srw = total(rw), missing by(year country type);
replace Srw = 1+Srw;

gen rwealthA = wealthA/wealth3 if type!=3;

/* Construct log variables */

foreach var of varlist mb fx relpi realfx gdpP gdpPpc pop book quant rw rwealthA {;
	gen L`var' = ln(`var');

	label var L`var' "log: `var'";
};

forval i = 1/2 {;
	gen LSrw`i' = ln(Srw)*(type==`i') if type!=3;
};

sort year country type counterpart;
by year country: gen LSrw3 = -ln(Srw[_N]) if type!=3 & type[_N]==3;

/* Construct dummy variables */

gen byte Iown = country==counterpart;
gen byte I_CR = country=="_CR";
gen byte I_OC = country=="_OC";

gsort year country type -Iown counterpart;
by year country type: gen byte Iycountry = _n==1;

gsort year counterpart type -Iown country;
by year counterpart type: gen byte Iycounterpart = _n==1;

gen Iycounter_euro = Iycounterpart;
replace Iycounter_euro = 0 if counterpart!="DEU" & counter_euro=="EUR";
replace Iycounter_euro = 0 if counterpart=="HKG" & counter_euro=="USA";

char year[omit] $year_max;
char counterpart[omit] USA;
char type[omit] 3;

xi i.year i.counterpart i.type, pre(I);

/* Construct lag variables */

foreach var of varlist Lmb Lfx {;
	sort Iycounterpart counterpart type year;
	by Iycounterpart counterpart type: gen `var'_ = `var'[_n-1] if Iycounterpart;

	gsort year counterpart type -Iycounterpart;
	by year counterpart type: replace `var'_ = `var'_[1];
};

/* Construct portfolio flows */

egen int year_country = min(year), by(country);

sort country counterpart type year;
by country counterpart type: gen flow = amount/exp(Lmb-Lmb_+Lfx-Lfx_)-amount[_n-1] if year>year_country & year==year[_n-1]+1;

replace flow = amount/exp(Lmb-Lmb_+Lfx-Lfx_) if year>year_country & missing(flow);
replace flow = amount if year>year_country & missing(flow);

egen flowT = total(flow), missing by(year country);

drop Lmb_ Lfx_ year_country;

/* Label variables */

order country_Name country_MSCI country_Ieuro country_group, after(country);
order counter_euro Name MSCI Ieuro group, after(counterpart);

label var country_Name		"Investor country name";
label var country_group		"Investor country group";
label var Name				"Issuer country name";

label var counter_euro		"Issuer country, Aggregate for euro ST debt";
label var group				"Issuer country group";

label var amount			"Investment amount (US$ billion)";
label var Qamount			"Investment quantity (LCU billion)";

label var market			"Market value, Aggregate for euro ST debt (US$ billion)";
label var Qmarket			"Market quantity, Aggregate for euro ST debt (LCU billion)";
label var book				"Book value, Aggregate for euro ST debt (US$ billion)";
label var quant				"PPP-adjusted quantity (US$ billion)";

label var wealth			"Wealth (US$ billion)";
label var wealthA			"Wealth by asset type (US$ billion)";
label var wealth3			"Wealth in equity (US$ billion)";
label var outside			"Outside wealth (US$ billion)";
label var outsideA			"Outside wealth by asset type (US$ billion)";

label var rw				"Ratio: amount to outsideA";
label var Srw				"Sum of rw by asset type";
label var rwealthA			"Ratio: wealthA to wealth3";

label var LSrw1				"log: Srw for ST debt";
label var LSrw2				"log: Srw for LT debt";
label var LSrw3				"log: Srw for equity";

label var Iown				"Dummy: Own country";
label var I_CR				"Dummy: Currency reserves";
label var I_OC				"Dummy: Other countries";
label var Iycountry			"Dummy: Unique sample by year, country, type";
label var Iycounterpart		"Dummy: Unique sample by year, counterpart, type";
label var Iycounter_euro	"Dummy: Unique sample by year, counter_euro, type";

label var flow				"Portfolio flow (US$ billion)";
label var flowT				"Total portfolio flow (US$ billion)";

label define group_label
	0 "Currency reserves"
	1 "DM: Americas"
	2 "DM: Europe & Middle East"
	3 "DM: Pacific"
	4 "EM"
	5 "Other";

label val country_group group group_label;

/* Save data */

sort year country counterpart type;

save Data4, replace;

log close;
